package com.hnkywl.sts.dao.jhgl;

import com.hnkywl.sts.entity.jhgl.Hkjh;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: ccr
 * Date: 13-5-2
 * Time: 上午10:51
 */
@Repository
public class HkjhDao extends SimpleHibernateTemplate<Hkjh, Long> {

    public Hkjh findByGsAndNfAndYf(Long gsid, Integer nf, Integer yf) {
        String hql = "from Hkjh where gsid = ? and nf = ? and yf = ?";
        return (Hkjh) findUnique(hql, gsid, nf, yf);
    }

    public Map<String, List> searchHkjh(Integer year, Integer month, Long xsgsId, Long xsbmId) {
        String sqlWith = "with temp as (select jh.id,jh.type,nbgs.id xsgsid,nbgs.qc xsgs,kh.id khid,kh.qc kh,jh.jhhkje from t_biz_hkjh jh \n" +
                " left join t_biz_ht xsht on xsht.id=jh.xsht_id and xsht.deleted=0 " +
                " left join t_biz_xsd xsd on xsd.id=jh.xsd_id and jh.deleted=0 " +
                " left join t_sys_gsjg nbgs on nbgs.id=jh.xsgs_id and nbgs.deleted=0 " +
                " left join t_sys_gsjg nbbm on nbbm.id=jh.xsbm_id and nbbm.deleted=0 " +
                " left join t_sys_gsjg kh on kh.id=jh.kh_id and kh.deleted=0 " +
                " where jh.deleted=0 and to_char(jh.date_created,'yyyy')=:year and to_char(jh.date_created,'MM')=:month @condition) ";
        String detailSql = " select id \"id\",xsgsid \"xsgsid\",xsgs \"xsgs\",khid \"khid\",kh \"kh\",sum(jhhkje) \"hj\",xsgsid \"_parentId\" from temp " +
                " group by id,xsgsid,xsgs,khid,kh " +
                " union " +
                " select xsgsid \"id\",xsgsid \"xsgsid\",xsgs \"xsgs\",null \"khid\",'小计' \"kh\",sum(jhhkje) \"hj\",null \"_parentId\"  from temp " +
                " group by xsgsid,xsgs";
        String totalSql = " select null \"id\",null \"xsgsid\",'合计' \"xsgs\",null \"khid\",null \"kh\",sum(jhhkje) \"hj\" from temp " +
                " group by xsgsid,xsgs";
        List<String> condition = new ArrayList<String>();
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("year", year);
        param.put("month", month);
        if (xsgsId != null) {
            condition.add(" nbgs.id=:gsId ");
            param.put("gsId", xsgsId);
        }
        if (xsbmId != null) {
            condition.add(" nbbm.id=:bmId ");
            param.put("bmId", xsbmId);
        }
        sqlWith = sqlWith.replaceAll("@condition", condition.isEmpty() ? "" : " and " + StringUtils.join(condition, "and"));
        List<Map> detailResult = createSQLQuery(sqlWith + detailSql).setProperties(param).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> totalResult = createSQLQuery(sqlWith + totalSql).setProperties(param).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, List> result = new HashMap<String, List>();
        result.put("detail", detailResult);
        result.put("total", totalResult);
        return result;
    }

    public Map<String, List> hkjhList(Long gsid, Long bmid, Date startDate, Date endDate) {
        if (gsid == null)
            gsid = -1L;
        if (bmid == null)
            bmid = -1L;
        //开始时间
        java.sql.Date pStartDate = new java.sql.Date(startDate.getTime());
        //结束时间
        java.sql.Date pEndDate = new java.sql.Date(endDate.getTime());
        //当前时间
        java.sql.Date pNowDate = new java.sql.Date(new Date().getTime());
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call RPT_CW_HKJH_TJ(?,?,?,?,?,?)}");
            call.setLong(1, gsid);
            call.setLong(2, bmid);
            call.setDate(3, pNowDate);
            call.setDate(4, pStartDate);
            call.setDate(5, pEndDate);
            call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(6);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            Map<String, List> result = new HashMap<String, List>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if ("1".equals(r.get("flag").toString()))
                    rows.add(r);
                if ("2".equals(r.get("flag").toString()))
                    rows.add(r);
                if ("3".equals(r.get("flag").toString()))
                    footer.add(r);
            }
            if (rows != null && !rows.isEmpty()) {
                result.put("rows", rows);
                result.put("footer", footer);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }
}
